package com.pet.system.util;

import java.io.FileOutputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFHyperlink;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;  
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;  
import org.apache.poi.ss.usermodel.CellStyle;  
import org.apache.poi.ss.usermodel.DataFormat;  
import org.apache.poi.ss.usermodel.Row;  
import org.apache.poi.ss.usermodel.Sheet;  
import org.apache.poi.ss.usermodel.Workbook;  
import org.apache.poi.ss.util.CellRangeAddress;  

import com.pet.framework.util.StringUtil;
import com.pet.system.constants.ATTSystem;

public class ExcelUtil {
	
	private static  HSSFCellStyle style =null;
	private static  HSSFCellStyle sumStyle =null;
	
	/**
	 public static String createFinanceExcel(String fileName,List<PackInfo> list,ReportBean rb){
		 HSSFWorkbook wb = new HSSFWorkbook();
		 HSSFSheet sheet = wb.createSheet(fileName);
		 int rowNum=0;
	     String[] title =  {"产品","日期","效果数","实际结算数","接入价格","放出价格","收入金额","支出金额","毛利"}; 

		 //draw title begin =============================
		 HSSFRow row = sheet.createRow(rowNum++);
		 row.setHeight((short)300);
		 int i=0;
		 createCellStyle(wb);
		 createSumCellStyle(wb);
		 for(String s:title){
			 HSSFCell cell = row.createCell(i);
			 sheet.setColumnWidth(i, 5000);
			 i++;
			 cell.setCellValue(s);
			 cell.setCellStyle(style);
		 }
		 //draw title end
		 //draw sum begin===================================
		 for(PackInfo pack:list){
			// 合并单元格(startRow，endRow，startColumn，endColumn)
			 sheet.addMergedRegion(new CellRangeAddress(rowNum,rowNum+pack.getList().size()-1,0,0));
			 HSSFRow srow = sheet.createRow(rowNum++);
			 HSSFCell cell = srow.createCell(0);
			 cell.setCellValue(pack.getPackName());
			 cell.setCellStyle(style);
			 boolean b=true;
			 HSSFRow detailRow;
			//0:packId 1:packName 2:createDate 3:actionCount 4:factCount 5:price 6:payPrice 7:getMoney 8:payMoney 9:money
			 for(Object[] obj:pack.getList()){
				 if(b){
					 detailRow=srow;
					 b=false;
				 }else{
					 detailRow=sheet.createRow(rowNum++);
				 }
				 detailRow.setHeight((short)300);
				 
				 
				 
				 setCell(detailRow, style, StringUtil.dealNull(obj[2]), 1);//date
				 setCell(detailRow, style, StringUtil.dealNull(obj[3]), 2);//action
				 setCell(detailRow, style, StringUtil.dealNull(obj[4]), 3);//fact
				 setCell(detailRow, style, StringUtil.dealNull(obj[5]), 4);//price
				 setCell(detailRow, style, StringUtil.dealNull(obj[6]), 5);//packprice
				 setCell(detailRow, style, StringUtil.dealNull(obj[7]), 6);//getmoney
				 setCell(detailRow, style, StringUtil.dealNull(obj[8]), 7);//paymoney
				 setCell(detailRow, style, StringUtil.dealNull(obj[9]), 8);//profit 
				 
			 }
			 //小计
			 HSSFRow sumrow = sheet.createRow(rowNum++);
			 sumrow.setHeight((short)300);
			 setCell(sumrow, sumStyle, "小计", 0);
			 setCell(sumrow, sumStyle, "", 1);//date
			 setCell(sumrow, sumStyle, pack.getSumActionCount(), 2);//action
			 setCell(sumrow, sumStyle, pack.getSumFactCount(), 3);//fact
			 setCell(sumrow, sumStyle, "", 4);//price
			 setCell(sumrow, sumStyle, "", 5);//packprice
			 setCell(sumrow, sumStyle, NumberUtil.format(pack.getGetMoney(),null), 6);//getmoney
			 setCell(sumrow, sumStyle, NumberUtil.format(pack.getPayMoney(),null), 7);//paymoney
			 setCell(sumrow, sumStyle, NumberUtil.format(pack.getProfit(), null), 8);//profit
		 }
		//draw sum done===================================
		//总计
		 
		 HSSFRow totalrow = sheet.createRow(rowNum++);
		 totalrow.setHeight((short)300);
		 setCell(totalrow, sumStyle, "总计", 0);
		 setCell(totalrow, sumStyle, "", 1);//date
		 setCell(totalrow, sumStyle, rb.getTotalActionCount(), 2);//action
		 setCell(totalrow, sumStyle, rb.getTotalFactCount(), 3);//fact
		 setCell(totalrow, sumStyle, "", 4);//price
		 setCell(totalrow, sumStyle, "", 5);//packprice
		 setCell(totalrow, sumStyle, NumberUtil.format(rb.getTotalGetMoney(),null), 6);//getmoney
		 setCell(totalrow, sumStyle, NumberUtil.format(rb.getTotalPayMoney(),null), 7);//paymoney
		 setCell(totalrow, sumStyle, NumberUtil.format(rb.getTotalProfit(), null), 8);//profit
		 
		 String path=ATTSystem.WEB_ROOT+"/userfiles/"+fileName+".xls";
		try {
			FileOutputStream os = new FileOutputStream( path);
			wb.write(os);
			os.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return path;
		 
	 }
	 */
	 
	private static void setCell(HSSFRow row,HSSFCellStyle style,Object obj,int n){
		HSSFCell cell = row.createCell(n);
		cell.setCellStyle(style);
		cell.setCellValue(StringUtil.dealNull(obj));
	}
	 
	 
	 private static void createCellStyle(HSSFWorkbook wb){
		    // 创建字体样式
		    HSSFFont font = wb.createFont();
		    font.setFontName("Verdana");
		    font.setBoldweight((short) 75);
		    font.setFontHeight((short) 225);
		    //font.setColor(HSSFColor.BLUE.index);
		    // 创建单元格样式
		    style = wb.createCellStyle();
		    style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
		    style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		    //style.setFillForegroundColor(HSSFColor.LIGHT_TURQUOISE.index);
		    //style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

		    // 设置边框
		    //style.setBottomBorderColor(HSSFColor.RED.index);
		    style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		    style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		    style.setBorderRight(HSSFCellStyle.BORDER_THIN);
		    style.setBorderTop(HSSFCellStyle.BORDER_THIN);
		    style.setFont(font);
		    DataFormat format = wb.createDataFormat(); 
		    style.setDataFormat(format.getFormat("#.##"));
		   
	 }
	 
	 
	 
	 private static void createSumCellStyle(HSSFWorkbook wb){
		    // 创建字体样式
		    HSSFFont font = wb.createFont();
		    font.setFontName("Verdana");
		    font.setBoldweight((short) 75);
		    font.setFontHeight((short) 225);
		    //font.setColor(HSSFColor.BLUE.index);
		    // 创建单元格样式
		    sumStyle = wb.createCellStyle();
		    sumStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
		    sumStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		    sumStyle.setFillForegroundColor(HSSFColor.ROSE.index);
		    //style.setFillBackgroundColor(HSSFColor.RED.index);
		    sumStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

		    // 设置边框
		    //style.setBottomBorderColor(HSSFColor.RED.index);
		    sumStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		    sumStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		    sumStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
		    sumStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
		    sumStyle.setFont(font);
		    DataFormat format = wb.createDataFormat(); 
		    sumStyle.setDataFormat(format.getFormat("#.##"));
		    
	 }
	 
	 
	 public static void main(String[] args) throws Exception {
		    // 创建Excel的工作书册 Workbook,对应到一个excel文档
		    HSSFWorkbook wb = new HSSFWorkbook();

		    // 创建Excel的工作sheet,对应到一个excel文档的tab
		    HSSFSheet sheet = wb.createSheet("sheet1");

		    // 设置excel每列宽度
		    sheet.setColumnWidth(0, 4000);
		    sheet.setColumnWidth(1, 3500);

		    // 创建字体样式
		    HSSFFont font = wb.createFont();
		    font.setFontName("Verdana");
		    font.setBoldweight((short) 100);
		    font.setFontHeight((short) 300);
		    font.setColor(HSSFColor.BLUE.index);

		    // 创建单元格样式
		    HSSFCellStyle style = wb.createCellStyle();
		    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		    style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		    style.setFillForegroundColor(HSSFColor.LIGHT_TURQUOISE.index);
		    style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

		    // 设置边框
		    style.setBottomBorderColor(HSSFColor.RED.index);
		    style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		    style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		    style.setBorderRight(HSSFCellStyle.BORDER_THIN);
		    style.setBorderTop(HSSFCellStyle.BORDER_THIN);

		    style.setFont(font);// 设置字体

		    // 创建Excel的sheet的一行
		    HSSFRow row = sheet.createRow(0);
		    row.setHeight((short) 500);// 设定行的高度
		    // 创建一个Excel的单元格
		    HSSFCell cell = row.createCell(0);

		    // 合并单元格(startRow，endRow，startColumn，endColumn)
		    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 2));

		    // 给Excel的单元格设置样式和赋值
		    cell.setCellStyle(style);
		    cell.setCellValue("hello world");

		    // 设置单元格内容格式
		    HSSFCellStyle style1 = wb.createCellStyle();
		    style1.setDataFormat(HSSFDataFormat.getBuiltinFormat("h:mm:ss"));

		    style1.setWrapText(true);// 自动换行

		    row = sheet.createRow(1);

		    // 设置单元格的样式格式

		    cell = row.createCell(0);
		    cell.setCellStyle(style1);
		    cell.setCellValue(new Date());

		    // 创建超链接
		    HSSFHyperlink link = new HSSFHyperlink(HSSFHyperlink.LINK_URL);
		    link.setAddress("http://www.baidu.com");
		    cell = row.createCell(1);
		    cell.setCellValue("百度");
		    cell.setHyperlink(link);// 设定单元格的链接

		    FileOutputStream os = new FileOutputStream("d:\\workbook.xls");
		    wb.write(os);
		    os.close();
		}
	 
	 public static void maina(String[] args) throws Exception {  
	        //创建一个EXCEL  
	        Workbook wb = new HSSFWorkbook();  
	        DataFormat format = wb.createDataFormat();  
	        CellStyle style;  
	        //创建一个SHEET  
	        Sheet sheet1 = wb.createSheet("产品清单");  
	        String[] title =  {"产品","日期","效果数","实际结算数","接入价格","放出价格","收入金额","支出金额","毛利"}; 
	        int i=0;  
	        //创建一行  
	        Row row = sheet1.createRow((short)0);  
	        //填充标题  
	        for (String  s:title){  
	            Cell cell = row.createCell(i);  
	            cell.setCellValue(s);  
	            i++;  
	        }  
	        Row row1 = sheet1.createRow((short)1);  
	        //下面是填充数据  
	       
	        row1.createCell(0).setCellValue(20071001);  
	        row1.createCell(1).setCellValue("金鸽瓜子");  
	        //创建一个单元格子  
	        Cell cell2=row1.createCell(2);  
	        // 填充产品价格  
	        cell2.setCellValue(2.45);  
	        style = wb.createCellStyle();  
	        style.setDataFormat(format.getFormat("#.##"));  
	        //设定样式  
	        cell2.setCellStyle(style);  
	        // 填充产品数量  
	        row1.createCell(3).setCellValue(200);  
	        /*   
	         * 定义显示日期的公共格式   
	         * 如:yyyy-MM-dd hh:mm   
	         * */  
	        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");     
	        String newdate = sdf.format(new Date());   
	        // 填充出产日期     
	        row1.createCell(4).setCellValue(newdate);  
	        row1.createCell(5).setCellValue("陕西西安");  
	        /*   
	         * 显示布尔值   
	         * */   
	        row1.createCell(6).setCellValue(true);  
	        /*   
	         * 合并单元格   
	         * 通过writablesheet.mergeCells(int x,int y,int m,int n);来实现的   
	         * 表示将first row, last row,first column,last column 
	         *    
	         * */    
	        Row row2 = sheet1.createRow((short) 2);  
	        Cell cell3 = row2.createCell((short) 0);  
	        cell3.setCellValue("合并了三个单元格");  
	        //第二行开始到第三行    到第0列开始到第0列
	        sheet1.addMergedRegion(new CellRangeAddress(2,8,0,0));  
	          
	       
	        
	        FileOutputStream fileOut = new FileOutputStream("d:\\testa.xls");  
	        wb.write(fileOut);  
	        
	        fileOut.close();  
	    }  
}
